package com.example.sp.controller;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.sp.entity.StuInfo;
import com.example.sp.entity.StuWork;
import com.example.sp.service.StuWorkService;
import com.example.sp.utils.Res;
import com.example.sp.utils.Result;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@RestController
@RequestMapping("/work")
@AllArgsConstructor
@Slf4j
public class StuWorkController {
    private final StuWorkService stuWorkService;

    /**
     * 按省份 每种岗位人数
     * @return
     */
    @RequestMapping("/workp")
    public Result selectwork1(){

        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>()
                .select("count(if(stu_status='企业',true,null)) as qiye",
                        "count(if(stu_status='行政',true,null)) as xingzheng",
                        "count(if(stu_status='事业',true,null)) as shiye",
                        "count(if(stu_status='升学',true,null)) as shengxue",
                        "count(if(stu_status='未就业',true,null)) as weijiuye",
                        "stu_status",
                        "province"
                ).groupBy("province")));
    }
    @RequestMapping("/workg")
    public Result selectwork2(){

        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>()
                .select("count(if(stu_status='企业',true,null)) as qiye",
                        "count(if(stu_status='行政',true,null)) as xingzheng",
                        "count(if(stu_status='事业',true,null)) as shiye",
                        "count(if(stu_status='升学',true,null)) as shengxue",
                        "count(if(stu_status='未就业',true,null)) as weijiuye",
                        "stu_grade"
                ).groupBy("stu_grade")));
    }
    @RequestMapping("/workg1")
    public Result selectwork5(String stuGrade){

        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>()
                .select("count(if(stu_status='企业',true,null)) as qiye",
                        "count(if(stu_status='行政',true,null)) as xingzheng",
                        "count(if(stu_status='事业',true,null)) as shiye",
                        "count(if(stu_status='升学',true,null)) as shengxue",
                        "count(if(stu_status='未就业',true,null)) as weijiuye",
                        "stu_grade"
                ).eq("stu_grade",stuGrade).groupBy("stu_grade")));
    }
    @RequestMapping("/workc")
    public Result selectwork3(){
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>()
                .select("count(if(stu_status='企业',true,null)) as qiye",
                        "count(if(stu_status='行政',true,null)) as xingzheng",
                        "count(if(stu_status='事业',true,null)) as shiye",
                        "count(if(stu_status='升学',true,null)) as shengxue",
                        "count(if(stu_status='未就业',true,null)) as weijiuye",
                        "colleage"
                ).groupBy("colleage")
        ));
    }
    @RequestMapping("/workpr")
    public Result selectwork4(){
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>()
                .select("count(if(stu_status='企业',true,null)) as qiye",
                        "count(if(stu_status='行政',true,null)) as xingzheng",
                        "count(if(stu_status='事业',true,null)) as shiye",
                        "count(if(stu_status='升学',true,null)) as shengxue",
                        "count(if(stu_status='未就业',true,null)) as weijiuye",
                        "profession"
                ).groupBy("profession")
        ));
    }
    @RequestMapping("/workpr1")
    public Result selectwork6(String profession){
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>()
                .select("count(if(stu_status='企业',true,null)) as qiye",
                        "count(if(stu_status='行政',true,null)) as xingzheng",
                        "count(if(stu_status='事业',true,null)) as shiye",
                        "count(if(stu_status='升学',true,null)) as shengxue",
                        "count(if(stu_status='未就业',true,null)) as weijiuye",
                        "profession"
                ).groupBy("profession").eq("profession",profession)
        ));
    }
    /**
     *
     * @param file
     * @return 数据库导入Excel
     */
    @PostMapping("/sc")
    @ResponseBody
    public String importExcel(@RequestParam("file") MultipartFile file) {

        try {
            ExcelReader reader= ExcelUtil.getReader(file.getInputStream());
            List<Map<String,Object>> readAll=reader.read(0,1,Integer.MAX_VALUE);
            List<StuWork> collect = readAll.stream().map(item -> BeanUtil.mapToBean(item, StuWork.class, true)).collect(Collectors.toList());
            stuWorkService.saveBatch(collect);
            System.out.println(readAll);
        } catch (IOException e) {
            e.printStackTrace();
        }

        return "OK";
    }

    /**
     *
     * @return 学院专业
     */
    @RequestMapping("/college")
    public Result cp(){
        List<Map<String,Object>> list1;
        list1=stuWorkService.listMaps(new QueryWrapper<StuWork>().select("colleage").groupBy("colleage"));
        List<Map<String,Object>> list2;
//        for (int i=0;i<list1.size();i++){
//            System.out.println(list1.get(i).get(0));
//        }
        return Res.ok(list1);
    }

    /**
     *
     * @param colleage
     * @return 所有专业
     */
   @RequestMapping("/pf")
    public Result pf(String colleage){
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>().select("profession").eq("colleage",colleage).groupBy("profession")));
    }

    /**
     *
     * @param profession
     * @return
     */
    @RequestMapping("/fs")
    public Result fs(String profession){
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>().select("count(if(stu_status='企业',true,null)) AS qy",
                "count(if(stu_status='事业',true,null)) AS sy",
                "count(if(stu_status='行政',true,null)) AS xz",
                "count(if(stu_status='升学',true,null)) AS sx",
                "count(if(stu_status='未就业',true,null)) AS wjy",
                "stu_grade","profession").eq("profession",profession).groupBy("stu_grade")
                ));
    }

    /**
     *
     * @param colleage
     * @return
     */
    @RequestMapping("/cs")
    public Result cs(String colleage){
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>().select("count(if(stu_status='企业',true,null)) AS qy",
                "count(if(stu_status='事业',true,null)) AS sy",
                "count(if(stu_status='行政',true,null)) AS xz",
                "count(if(stu_status='升学',true,null)) AS sx",
                "count(if(stu_status='未就业',true,null)) AS wjy",
                "stu_grade","colleage") .eq("colleage",colleage).groupBy("stu_grade")
               ));
    }

    /**
     *
     * @return
     */
    @GetMapping("/gn")
    public Result select10() {
        return Res.ok(stuWorkService.listMaps(new QueryWrapper<StuWork>().select(
                "count(if(stu_status='企业',true,null)) AS qy",
                "count(if(stu_status='事业',true,null)) AS sy",
                "count(if(stu_status='行政',true,null)) AS xz",
                "count(if(stu_status='升学',true,null)) AS sx",
                "count(if(stu_status='未就业',true,null)) AS wjy",
                "stu_grade").groupBy("stu_grade")));
    }
    /**
     * 每个学院 按专业分
     * @return
     */
    @GetMapping("/zy")
    public Result select11(String profession,String colleage) {
        return Res.ok(stuWorkService.list(new LambdaQueryWrapper<StuWork>()
                .eq(StrUtil.isNotEmpty(profession),StuWork::getProfession,profession)
                .eq(StrUtil.isNotEmpty(colleage),StuWork::getColleage,colleage)));
    }


}
